IF NOT EXISTS (SELECT * FROM sys.databases where name='ServicesDirectory')
drop database ServicesDirectory

CREATE DATABASE ServicesDirectory
GO
USE ServicesDirectory
GO

----------------REGION FOR CREATING TABLES-----------------------------

CREATE TABLE ContactTypes
(
	ContactTypeId int IDENTITY(1,1) NOT NULL PRIMARY KEY,
	ContactTypeName nvarchar(100) NOT NULL,
	CONSTRAINT uc_ContactTypes_ContactTypeName UNIQUE(ContactTypeName)
)

CREATE TABLE BestContactMethods
(
	BestContactMethodId int IDENTITY(1,1) NOT NULL PRIMARY KEY,
	BestContactMethodName nvarchar(100) NOT NULL,
	CONSTRAINT uc_BestContactMethods_BestContactMethodName UNIQUE(BestContactMethodName)
)

CREATE TABLE Contacts
(
	ContactId int IDENTITY(1,1) NOT NULL PRIMARY KEY,
	FirstName nvarchar(50) NOT NULL,
	Surname nvarchar(50) NOT NULL,
	KnownAs nvarchar(50) NULL,
	OfficePhone varchar(50) NULL,
	MobilePhone varchar(50) NULL,
	HomePhone varchar(50) NULL,
	Email varchar(50) NULL,
	ManageId int NULL,
	ContactTypeId int NOT NULL,
	BestContactMethodId int NULL,
	JobRole nvarchar(50) NULL,
	WorkBase nvarchar(50) NOT NULL,
	JobTitle nvarchar(50) NULL,
	IsActive bit NOT NULL DEFAULT(1),
	CONSTRAINT  fk_Contacts_ContactTypeId FOREIGN KEY (ContactTypeId)
	REFERENCES ContactTypes(ContactTypeId),
	CONSTRAINT  fk_Contacts_BestContactMethodId FOREIGN KEY (BestContactMethodId)
	REFERENCES BestContactMethods(BestContactMethodId)
)

CREATE TABLE Programs
(
	ProgramId int IDENTITY(1,1) NOT NULL PRIMARY KEY,
	ProgramName nvarchar(100) NOT NULL,
	Description nvarchar(100) NULL,
	ContactId int NULL,
	IsActive bit NOT NULL DEFAULT(1),
	CONSTRAINT	UC_Programs_ProgramName UNIQUE(ProgramName),
	CONSTRAINT fk_Programs_ContactId FOREIGN KEY (ContactId)
	REFERENCES Contacts(ContactId),
)

CREATE TABLE Countries
(
	CountryId varchar(20) NOT NULL PRIMARY KEY,
	CountryName varchar(50) NOT NULL,
	
	CONSTRAINT uc_Countries_CountryName UNIQUE(CountryName)
)


CREATE TABLE GovOfficeRegions
(
	GovOfficeRegionId int IDENTITY(1,1) NOT NULL PRIMARY KEY,
	GovOfficeRegionName nvarchar(50) NOT NULL,
	Description nvarchar(256) NULL,
	IsActive bit NOT NULL DEFAULT(1),

	CONSTRAINT uc_GovOfficeRegions_GovOfficeRegionName UNIQUE(GovOfficeRegionName)
)

CREATE TABLE Counties
(
	CountyId varchar(20) NOT NULL PRIMARY KEY,
	CountyName varchar(50) NOT NULL,
	CountryId varchar(20) NOT NULL,
	GovOfficeRegionId int NOT NULL
	CONSTRAINT  fk_Counties_CountryId FOREIGN KEY (CountryId)
	REFERENCES	Countries(CountryId),
	CONSTRAINT  fk_Counties_GovOfficeRegionId FOREIGN KEY (GovOfficeRegionId)
	REFERENCES GovOfficeRegions(GovOfficeRegionId),
	CONSTRAINT uc_Counties_CountyName UNIQUE(CountyName)
)

CREATE TABLE Towns
(
	TownId varchar(20) NOT NULL PRIMARY KEY,
	TownName varchar(50) NOT NULL,
	CountyId varchar(20) NOT NULL,

	CONSTRAINT  fk_Towns_CountyId FOREIGN KEY (CountyId)
	REFERENCES Counties(CountyId),
	CONSTRAINT uc_Towns_TownId UNIQUE(TownId)
)

CREATE TABLE Addresses
(
	AddressId int IDENTITY(1,1) NOT NULL PRIMARY KEY,
	AddressLine nvarchar(250) NOT NULL,
	AddressLine2 nvarchar(250) NULL,
	AddressLine3 nvarchar(250) NULL,
	PostCode varchar(20) NOT NULL,
	TownId varchar(20) NOT NULL,
	CONSTRAINT  fk_Addresses_TownId FOREIGN KEY (TownId)
	REFERENCES Towns(TownId)
)


CREATE TABLE BusinessTypes
(
	BusinessId int IDENTITY(1,1) NOT NULL PRIMARY KEY,
	BusinessName varchar(250) NOT NULL,
	SICCode varchar(5) NOT NULL,

	CONSTRAINT uc_BusinessTypes_BusinessName UNIQUE(BusinessName)
)

CREATE TABLE Roles
(
	RoleId int IDENTITY(1,1) NOT NULL PRIMARY KEY,
	RoleName varchar(50) NOT NULL,

	CONSTRAINT uc_Roles_RoleName UNIQUE(RoleName)
)

CREATE TABLE Users
(
	UserId int IDENTITY(1,1) NOT NULL PRIMARY KEY,
	Account varchar(50) NOT NULL,
	Password varchar(50) NOT NULL,
	RoleId int NOT NULL,
	Email varchar(250) NOT NULL,
	IsActive bit NOT NULL,

	CONSTRAINT fk_Users_RoleId FOREIGN KEY(RoleId)
	REFERENCES Roles(RoleId),
	CONSTRAINT uc_Users_Account UNIQUE(Account),
	CONSTRAINT uc_Users_Email UNIQUE(Email)
)

-- drop table Organizations
CREATE TABLE Organizations(
	OrganizationId		INT IDENTITY(1,1) NOT NULL,
	OrganizationName	Nvarchar(250)	NOT NULL,
	ShortDescription	nvarchar(500)	NOT NULL,
	AddressId			int				NOT NULL,
	BusinessId			int				NOT NULL,
	PhoneNumber			varchar(20)		NOT NULL,
	CityTown			nvarchar(50)	NULL,
	County				nvarchar(50)	NULL,
	CountryId			varchar(20)		NULL,
	PreferredOrganization	bit			NULL,
	FullDescription		nvarchar(MAX)	NULL,
	ContactId			int				NULL,
	Fax					varchar(20)		NULL,
	Email				varchar(50)		NULL,
	WebAddress			nvarchar(250)	NULL,
	CharityNumber		varchar(20)		NULL,
	CompanyNumber		varchar(20)		NULL,
	OrganizationSpecialism	varchar(100)	NULL,
	ServiceDisabilities	varchar(100)	NULL,
	ServiceBarriers		varchar(100)	NULL,
	ServiceBenefits		varchar(100)	NULL,
	ServiceCircumstances	varchar(100)	NULL,
	ServiceEthnicity	varchar(100)	NULL,
	Accreditation		varchar(100)	NULL,	
	IsActive			bit				NOT NULL DEFAULT (1),
	CONSTRAINT	PK_Organizations PRIMARY KEY(OrganizationId),
	CONSTRAINT	UC_Organizations_Name UNIQUE(OrganizationName),
	CONSTRAINT	FK_Organizations_ContactId FOREIGN KEY(ContactId) REFERENCES Contacts(ContactId),
	CONSTRAINT	FK_Organizations_AddressId	FOREIGN KEY(AddressId) REFERENCES Addresses(AddressId),
	CONSTRAINT	FK_Organizations_TypeOfBusinessId FOREIGN KEY(BusinessId) REFERENCES BusinessTypes(BusinessId),
	CONSTRAINT	FK_Organizations_CountryId	FOREIGN KEY(CountryId) REFERENCES Countries(CountryId)
)
GO

-- drop table OrganizationProgram
CREATE TABLE OrganizationProgram(
	OrganizationId		int	NOT NULL,
	ProgramId			int	NOT NULL,
	CONSTRAINT			PK_OrganizationProgram primary key(OrganizationId, ProgramId),
	CONSTRAINT			FK_OrganizationProgram_OrganizationId foreign key(OrganizationId)
		references Organizations(OrganizationId),
	CONSTRAINT			FK_OrganizationProgram_ProgramId foreign key(ProgramId)
		references Programmes(ProgrammeId)
)
GO

CREATE TABLE OrganizationReferences(
	Id					int				NOT NULL PRIMARY KEY,
	Category			int				NOT NULL,
	ItemName			nvarchar(250)	NOT NULL
)
GO
-- drop table SupportingMaterials
-- truncate table SupportingMaterials
CREATE TABLE SupportingMaterials(
	SupportingMaterialId	int	identity(1,1)	NOT NULL,
	Url						nvarchar(250)		NOT NULL,
	[Description]			nvarchar(250)		NULL,
	[Type]					varchar(20)			NULL,
	UserId					int					NOT NULL,
	AddedDate				smalldatetime		NOT NULL DEFAULT GETDATE(),
	IsActive				bit					NOT NULL DEFAULT 1,
	OrganizationId			int					NOT NULL,
	CONSTRAINT				PK_SupportingMaterials primary key(SupportingMaterialId),
	CONSTRAINT				FK_SupportingMaterials_UserId foreign key(UserId) references Users(UserId),
	CONSTRAINT				FK_SupportingMaterials_OrganizationId foreign key(OrganizationId) references 
		Organizations(OrganizationId)
)
GO
-- drop table Directorates
CREATE TABLE Directorates(
	DirectorateId			int identity(1,1)	NOT NULL,
	DirectorateName			nvarchar(50)		NOT NULL,
	BusinessId				int					NOT NULL,
	AddressId				int					NOT NULL,
	ContactId				int					NULL,
	ShortDescription		nvarchar(500)		NULL,	
	FullDescription			nvarchar(max)		NULL,
	CityTown				nvarchar(50)		NULL,
	County					nvarchar(50)		NULL,
	CountryId				varchar(20)		NULL,		
	PhoneNumber				varchar(20)		NULL,
	Fax						varchar(20)		NULL,
	Email					varchar(50)		NULL,
	WebAddress				nvarchar(250)	NULL,
	CharityNumber			varchar(20)		NULL,
	CompanyNumber			varchar(20)		NULL,
	IsActive				bit				NOT NULL DEFAULT 1,
	OrganizationId			int				NOT NULL,
	CONSTRAINT				PK_Directorates	primary key(DirectorateId),
	CONSTRAINT				UC_Directorates_DirectorateName unique(DirectorateName),
	CONSTRAINT				FK_Directorates_OrganizationId	foreign key(OrganizationId) references Organizations(OrganizationId),
	CONSTRAINT				FK_Directorates_BusinessId	foreign key(BusinessId) references BusinessTypes(BusinessId),
	CONSTRAINT				FK_Directorates_AddressId	foreign key(AddressId)	references Addresses(AddressId),
	CONSTRAINT				FK_Directorates_ContactId	foreign key(ContactId)	references Contacts(ContactId),
	CONSTRAINT				FK_Directorates_CountryId	FOREIGN KEY(CountryId) REFERENCES Countries(CountryId)
)
GO
-- drop table Departments
CREATE TABLE Departments(
	DepartmentId			int identity(1,1)	NOT NULL,
	DepartmentName			nvarchar(100)		NOT NULL,
	BusinessId				int					NULL,
	AddressId				int					NULL,
	ContactId				int					NULL,
	ShortDescription		nvarchar(500)		NOT NULL,	
	FullDescription			nvarchar(max)		NULL,
	CityTown				nvarchar(50)		NULL,
	County					nvarchar(50)		NULL,
	CountryId				varchar(20)			NULL,		
	PhoneNumber				varchar(20)		NULL,
	Fax						varchar(20)		NULL,
	Email					varchar(50)		NULL,
	WebAddress				nvarchar(250)	NULL,
	IsActive				bit				NOT NULL DEFAULT 1,
	DirectorateId			int				NOT NULL,
	CONSTRAINT				PK_Departments	primary key(DepartmentId),
	CONSTRAINT				UC_Departments_DepartmentName unique(DepartmentName),
	CONSTRAINT				FK_Departments_DirectorateId	foreign key(DirectorateId) references Directorates(DirectorateId),
	CONSTRAINT				FK_Departments_BusinessId	foreign key(BusinessId) references BusinessTypes(BusinessId),
	CONSTRAINT				FK_Departments_AddressId	foreign key(AddressId)	references Addresses(AddressId),
	CONSTRAINT				FK_Departments_ContactId	foreign key(ContactId)	references Contacts(ContactId),
	CONSTRAINT				FK_Departments_CountryId	FOREIGN KEY(CountryId) REFERENCES Countries(CountryId)
)
GO
-- drop table Teams
CREATE TABLE Teams(
	TeamId					int identity(1,1)	NOT NULL,
	TeamName				nvarchar(100)		NOT NULL,
	BusinessId				int					NULL,
	AddressId				int					NULL,
	ContactId				int					NULL,
	ShortDescription		nvarchar(500)		NULL,	
	FullDescription			nvarchar(max)		NULL,
	CityTown				nvarchar(50)		NULL,
	County					nvarchar(50)		NULL,
	CountryId				varchar(20)		NULL,		
	PhoneNumber				varchar(20)		NULL,
	Fax						varchar(20)		NULL,
	Email					varchar(50)		NULL,
	WebAddress				nvarchar(250)	NULL,
	IsActive				bit				NOT NULL DEFAULT 1,
	DepartmentId			int				NOT NULL,
	CONSTRAINT				PK_Teams	primary key(TeamId),
	CONSTRAINT				UC_Teams_DepartmentName unique(TeamName),
	CONSTRAINT				FK_Teams_DepartmentId foreign key(DepartmentId) references Departments(DepartmentId),
	CONSTRAINT				FK_Teams_BusinessId	foreign key(BusinessId) references BusinessTypes(BusinessId),
	CONSTRAINT				FK_Teams_AddressId	foreign key(AddressId)	references Addresses(AddressId),
	CONSTRAINT				FK_Teams_ContactId	foreign key(ContactId)	references Contacts(ContactId),
	CONSTRAINT				FK_Teams_CountryId	FOREIGN KEY(CountryId) REFERENCES Countries(CountryId)			
)
GO

CREATE TABLE OrganizationPrograms(
	ProgramId				int			NOT NULL,
	OrganizationId			int			NOT NULL,
	CONSTRAINT	FK_OrganizationPrograms_ProgramId foreign key(ProgramId) references Programs(ProgramId),
	CONSTRAINT	FK_OrganizationPrograms_OrganizationId foreign key(OrganizationId) references Organizations(OrganizationId),
	CONSTRAINT	PK_OrganizationPrograms primary key(ProgramId,OrganizationId)
)
GO

CREATE TABLE [Services](
	ServiceId				int identity(1,1)	NOT NULL,
	ServiceName				nvarchar(100)	NOT NULL,
	ShortDescription		nvarchar(500)	NOT NULL,
	SubType					varchar(50)		NOT NULL,
	ContactId				int				NOT NULL,
	ClientDescription		nvarchar(500)	NULL,
	Attendance				nvarchar(100)	NULL,
	StartExpected			smalldatetime	NULL,
	StartDate				smalldatetime	NULL,
	EndDate					smalldatetime	NULL,
	ExtendableYears			int				NULL,
	ExtendableMonths		int				NULL,
	FullDescription			nvarchar(max)	NULL,
	DepartmentId			int				NOT NULL,
	ServiceType				varchar(100)	NOT NULL,
	DescriptionDelivery		nvarchar(500)	NULL,
	ContractCode			nvarchar(100)	NULL,
	ContractValue			nvarchar(100)	NULL,
	ContractStagedPayment	bit				NULL DEFAULT 0,
	ReferrealProcess		nvarchar(100)	NULL,
	LimitedYears			int				NULL,
	LimitedMonths			int				NULL,
	IsActive				bit				NOT NULL DEFAULT 1,
	CONSTRAINT	PK_Services PRIMARY KEY(ServiceId),
	CONSTRAINT	FK_Services_ContactId FOREIGN KEY (ContactId) REFERENCES Contacts(ContactId),
	CONSTRAINT	FK_Services_DepartmentId FOREIGN KEY(DepartmentId) REFERENCES Departments(DepartmentId),
	CONSTRAINT	UC_Services_ServiceName	UNIQUE(ServiceName)
)
GO
CREATE INDEX IX_Services_ServiceName ON [Services](ServiceName)
GO

CREATE TABLE ServiceReferenceDatas(
	Id						int				NOT NULL,
	CategoryId				int				NOT NULL,
	ItemName				nvarchar(250)	NOT NULL,
	CONSTRAINT	PK_ServiceReferences primary key(Id)
)
GO

CREATE TABLE Interventions(
	InterventionId			int identity(1,1)	NOT NULL,
	InterventionName		nvarchar(250)	NOT NULL,
	ServiceId				int				NULL,
	IsActive				bit				NOT NULL DEFAULT 1,
	CONSTRAINT	PK_Interventions primary key(InterventionId),
	CONSTRAINT	FK_Interventions_ServiceId foreign key(ServiceId) references [Services](ServiceId),
)
GO

CREATE TABLE ServicePrograms(
	ProgramId				int			NOT NULL,
	ServiceId				int			NOT NULL,
	CONSTRAINT	FK_ServicePrograms_ProgramId foreign key(ProgramId) references Programs(ProgramId),
	CONSTRAINT	FK_ServicePrograms_ServiceId foreign key(ServiceId) references [Services](ServiceId),
	CONSTRAINT	PK_ServicePrograms primary key(ProgramId,ServiceId)
)

CREATE TABLE ServiceReferences(
	ServiceId				int				NOT NULL,
	BenefitsCriterion		varchar(100)	NULL,
	BarriersCriterion		varchar(100)	NULL,
	EthnicityCriterion		varchar(100)	NULL,
	DisabilityCriterion		varchar(100)	NULL,
	PersonalCircumstancesCriterion	varchar(100)	NULL,
	ParticipationCriterion	varchar(100)	NULL,
	
	ClientSupportProcess	varchar(100)	NULL,
	ClientJourney			varchar(100)	NULL,
	SupportCentres			varchar(100)	NULL,
	ClientOutcome			varchar(100)	NULL,
	TargetClient			varchar(100)	NULL,
	Accreditations			varchar(100)	NULL,
	ReferralSources			varchar(100)	NULL,

	ProgramId				int				NULL,
	CONSTRAINT	PK_ServiceReferences primary key(ServiceId),
	CONSTRAINT	FK_ServiceReferences_ServiceId foreign key(ServiceId) references [Services](ServiceId),
	CONSTRAINT	FK_ServiceReferences_ProgramId foreign key(ProgramId) references Programs(ProgramId)
)
GO

CREATE TABLE ServiceContracts(
	ServiceId				int				NOT NULL,
	ContractOutcome			varchar(100)	NULL,
	ContractObligation		varchar(100)	NULL,
	Participation			varchar(20)		NULL,
	CONSTRAINT	PK_ServiceContracts primary key(ServiceId),
	CONSTRAINT	FK_ServiceContracts_ServiceId foreign key(ServiceId) references [Services](ServiceId)
)
GO

CREATE TABLE ServiceFundings(
	ServiceId				int				NOT NULL,
	FundingContactId		int				NOT NULL,
	FundingAmount			float			NOT NULL,
	FundingStart			smalldatetime	NULL,
	FundingEnd				smalldatetime	NULL,
	FundraisingForText		nvarchar(500)	NULL,
	FundraisingWhy			nvarchar(500)	NULL,
	FundraisingDonorAmount	float			NULL,
	FundingNeeds			float			NULL,
	FundingContinuationAmount	float			NULL,
	FundingCondinuationDetails	nvarchar(500)	NULL,
	FundraisingNeeds			float			NULL,
	FundraisingRequiredBy		smalldatetime	NULL,
	FundraisingCompletedDate	smalldatetime	NULL,
	FundraisingDonationDate		smalldatetime	NULL,
	FundraisingDonationIncremental	bit		NULL DEFAULT 0,
	CONSTRAINT	PK_ServiceFundings primary key(ServiceId),
	CONSTRAINT	FK_ServiceFundings_ServiceId foreign key(ServiceId) references [Services](ServiceId),
	CONSTRAINT	FK_ServiceFundings_ContactId foreign key(ContactId) references Contacts(ContactId)
)
GO

CREATE TABLE TrustRegions(
	TrustRegionId			int	identity(1,1)	NOT NULL,
	TrustRegionName			nvarchar(250)	NOT NULL,
	CountryId				int				NOT NULL,
	[Description]			nvarchar(500)	NULL,
	IsActive				bit				NOT NULL DEFAULT 1,
	CONSTRAINT				PK_TrustRegions primary key(TrustRegionId),
	CONSTRAINT				FK_TrustRegions_CountryId	foreign key(CountryId) references Countries(CountryId)
)
GO

CREATE TABLE TrustDistricts(
	TrustDistrictId			int	identity(1,1)	NOT NULL,
	TrustDistrictName		nvarchar(250)		NOT NULL,
	[Description]			nvarchar(500)		NULL,
	IsActive				bit				NOT NULL DEFAULT 1,
	CONSTRAINT				PK_TrustDistricts primary key(TrustRegionId),
)
GO

--drop table Premises
CREATE TABLE Premises(
	PremiseId				int identity(1,1)	NOT NULL,
	--PremiseName				nvarchar(250)		NOT NULL,
	LocationName			nvarchar(250)		NOT NULL,
	LocationType			nvarchar(250)		NOT NULL,
	LocationStatus			nvarchar(250)		NOT NULL,
	PhoneNumber				varchar(20)			NOT NULL,
	LocationStatusDate		smalldatetime		NULL,
	LocationOrganization	int					NULL,
	ShopFlagDate			smalldatetime		NULL,
	LocationDescription		nvarchar(250)		NULL,
	AddressId				int					NULL,
	CityTown				nvarchar(50)		NULL,
	County					nvarchar(50)		NULL,
	CountryId				varchar(20)			NULL,	
	KnowAs					nvarchar(50)		NULL,
	ProjectCode				nvarchar(50)		NULL,
	FaxNumber				varchar(20)			NULL,
	MinicomNumber			varchar(20)			NULL,
	PrimaryLocation			bit					NULL,
	LocationManaged			bit					NULL,
	NetworkConnectivity		bit					NULL,
	IsNewShop				bit					NULL,
	IsActive				bit					NOT NULL DEFAULT 1,
	SpecialistShop			bit					NULL,
	CONSTRAINT				PK_Premises			primary key(PremiseId),
	CONSTRAINT				FK_Premises_AddressId	foreign key(AddressId) references Addresses(AddressId),
	CONSTRAINT				FK_Premise_CountryId	foreign key(CountryId) references Countries(CountryId)
	--CONSTRAINT				FK_PRemises_Contactd	foreign key(LocationOrganization) references Contacts(ContactId)
)

CREATE TABLE 
---------------END REGION FOR CREATING TABLES----------------------------


---------------BEGIN REGION FOR INSERTING DATA---------------------------

---------------END REGION FOR INSERTING DATA-----------------------------


--Table dbo.Addresses
